<?php

/*
 * SQL生成器相关函数
 * @since 1.0 2014-9-2 by sutroon
 */

/**
 * 生成插入脚本
 * @param array $arrschema 表结构数组
 * @return array array(普通的,精简的,默认值的)
 * @since 1.0 2014-8-27 by sutroon
 * @example
 *  $arrout=get_insert_sql('tuser');
 *  echo $arrout[0] . PHP_EOL;
 * echo '=========Lite============' . PHP_EOL;
 * echo $arrout[1] . PHP_EOL;
 * echo '=========Default=========' . PHP_EOL;
 * echo $arrout[2] . PHP_EOL;
 */
function get_insert_table_sql($table) {
    $str = '';
    $strlite = '';
    $vals = '';
    $valslite = '';
    $valsdefault = '';
    $arr = get_table_schema_array($table);
    foreach ($arr as $row) {
        $str .= '`' . $row['Field'] . '`, ';
        $vals .= get_type_field($row) . ', ';
        $valsdefault .= get_type_field($row, true) . ', ';
        if ($row['Null'] == 'NO' && !is_null($row['Default'])) {
            continue;
        }
        $strlite .= '`' . $row['Field'] . '`, ';
        $valslite .= get_type_field($row) . ', ';
    }
    if ($str) {
        $str = substr($str, 0, strlen($str) - 2);
        $vals = substr($vals, 0, strlen($vals) - 2);
        $valsdefault = substr($valsdefault, 0, strlen($valsdefault) - 2);
    }
    if ($strlite) {
        $strlite = substr($strlite, 0, strlen($strlite) - 2);
        $valslite = substr($valslite, 0, strlen($valslite) - 2);
    }
    // 普通的,精简的,默认值的
    return array("insert into $table ($str) values ($vals);", "insert into $table ($strlite) values ($valslite);", "insert into $table ($str) values ($valsdefault);");
}

/**
 * 获取查询表的语句
 * @param string $table
 * @return string
 * @since 1.0 2014-8-27 by sutroon
 */
function get_select_table_sql($table) {
    $str = '';
    $arr = get_table_schema_array($table);
    foreach ($arr as $row) {
        $str .= '`' . $row['Field'] . '`, ';
    }
    if ($str) {
        $str = substr($str, 0, strlen($str) - 2);
    }
    return "select $str from $table;";
}

/**
 * 获取更新数据表的语句
 * @param string $table
 * @return string
 * @since 1.0 2014-8-27 by sutroon
 */
function get_update_table_sql($table) {
    $str = '';
    $where = '';
    $i = 0;
    $arr = get_table_schema_array($table);
    foreach ($arr as $row) {
        if ($i == 0) {
            $where = '`' . $row['Field'] . '`=' . get_type_field($row);
        } else {
            $str .= '`' . $row['Field'] . '` = ' . get_type_field($row) . ', ';
        }
        $i++;
    }
    if ($str) {
        $str = substr($str, 0, strlen($str) - 2);
    }
    return "update $table set $str where $where;";
}

/**
 * 获取更新重置字段的语句
 * @param string $table
 * @return string
 * @since 1.0 2014-9-11 by sutroon
 */
function get_update_reset_table_sql($table) {
    $str = '';
    $where = '';
    $i = 0;
    $arr = get_table_schema_array($table);
    foreach ($arr as $row) {
        if ($i == 0) {
            $where = $row['Field'] . '=' . get_type_value($row);
        } else {
            $str .= $row['Field'] . '=' . get_type_value($row) . ', ';
        }
        $i++;
    }
    if ($str) {
        $str = substr($str, 0, strlen($str) - 2);
    }
    return "update $table set $str where $where;";
}

/**
 * 获取从数据表生成创建视图的语句
 * @param string $table
 * @return string
 * @since 1.0 2014-8-27 by sutroon
 */
function get_create_view_from_table_sql($table) {
    $char = '';
    if (strrpos($table, '_') !== false) {
        $char = substr($table, strrpos($table, '_') + 1, 1);
    } else {
        $char = $table[1];
    }
    $str = '';
    $arr = get_table_schema_array($table);
    foreach ($arr as $row) {
        $str .= $char . '.' . $row['Field'] . ', ';
    }
    if ($str) {
        $str = substr($str, 0, strlen($str) - 2);
    }
    return 'create view if not exists v' . substr($table, 1) . ' as' . PHP_EOL . 'create or replace view v' . substr($table, 1) . ' as' . PHP_EOL . "\t" . 'select' . PHP_EOL . "\t" . $str . PHP_EOL . "\t" . "from $table $char;";
}
